﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.IO;
using System.Linq;
using System.Linq.Dynamic.Core;
using System.Threading.Tasks;
using Acme.BookStore.Authors;
using Acme.BookStore.Localization;
using Acme.BookStore.Permissions;
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Caching.Distributed;
using Microsoft.Extensions.Localization;
using OfficeOpenXml;
using Scriban.Runtime.Accessors;
using Volo.Abp;
using Volo.Abp.Application.Dtos;
using Volo.Abp.Application.Services;
using Volo.Abp.Caching;
using Volo.Abp.Data;
using Volo.Abp.DependencyInjection;
using Volo.Abp.Domain.Entities;
using Volo.Abp.Domain.Repositories;
using Volo.Abp.ObjectMapping;
using Volo.Abp.Validation;

namespace Acme.BookStore.Books
{
    [Authorize(BookStorePermissions.Books.Default)]
    public class BookAppService
        : CrudAppService<Book, BookDto, Guid, GetBookListDto, CreateUpdateBookDto>,
        IBookAppService, ITransientDependency
    {
        private readonly IAuthorRepository _authorRepository;
        private readonly IDistributedCache<BookCacheItem> _cache;
        private readonly IDataFilter _dataFilter;
        private readonly IStringLocalizer<BookStoreResource> _localizer;
        public BookAppService(
            IRepository<Book, Guid> repository,
            IAuthorRepository authorRepository,
            IDistributedCache<BookCacheItem> cache,
            IDataFilter dataFilter,
            IStringLocalizer<BookStoreResource> localizer)
            : base(repository)
        {
            _authorRepository = authorRepository;
            GetPolicyName = BookStorePermissions.Books.Default;
            GetListPolicyName = BookStorePermissions.Books.Default;
            CreatePolicyName = BookStorePermissions.Books.Create;
            UpdatePolicyName = BookStorePermissions.Books.Edit;
            DeletePolicyName = BookStorePermissions.Books.Delete;
            _cache = cache;
            _dataFilter = dataFilter;
            _localizer = localizer;
        }
        public override async Task<BookDto> GetAsync(Guid id)
        {
            var queryable = await Repository.GetQueryableAsync();

            var query = from book in queryable
                        join author in await _authorRepository.GetQueryableAsync()
                        on book.AuthorId equals author.Id
                        where book.Id == id
                        select new { book, author };

            var queryResult = await AsyncExecuter.FirstOrDefaultAsync(query);
            if (queryResult == null)
            {
                throw new EntityNotFoundException(typeof(Book), id);
            }

            var bookDto = ObjectMapper.Map<Book, BookDto>(queryResult.book);
            bookDto.AuthorName = queryResult.author.Name;
            return bookDto;
        }
        public async Task<BookCacheItem> GetByCacheAsync(Guid id)
        {
            return await _cache.GetOrAddAsync(
                id.ToString(), //缓存键
                async () => await GetBookFromDatabaseAsync(id),
                () => new DistributedCacheEntryOptions
                {
                    AbsoluteExpiration = DateTimeOffset.Now.AddHours(1)
                }
            );
        }
        private async Task<BookCacheItem> GetBookFromDatabaseAsync(Guid bookId)
        {
            //TODO: 从数据库获取数据
            var queryable = await Repository.GetQueryableAsync();

            var query = from book in queryable
                        join author in await _authorRepository.GetQueryableAsync()
                        on book.AuthorId equals author.Id
                        where book.Id == bookId
                        select new { book, author };

            var queryResult = await AsyncExecuter.FirstOrDefaultAsync(query);
            if (queryResult == null)
            {
                throw new EntityNotFoundException(typeof(Book), bookId);
            }

            var bookDto = ObjectMapper.Map<Book, BookCacheItem>(queryResult.book);
            bookDto.AuthorName = queryResult.author.Name;
            return bookDto;
        }


        public async Task<List<BookDto>> GetAllBook()
        {
            using (_dataFilter.Disable<ISoftDelete>())
            {
                var queryable = await Repository.GetQueryableAsync();

                var queryResult = await AsyncExecuter.ToListAsync(queryable);
                return ObjectMapper.Map<List<Book>, List<BookDto>>(queryResult);
            }
        }

        public override async Task<PagedResultDto<BookDto>> GetListAsync(GetBookListDto input)
        {
            var queryable = await Repository.GetQueryableAsync();
            queryable = queryable
                    .WhereIf(!string.IsNullOrWhiteSpace(input.Name), q => q.Name.Contains(input.Name));
            var authorQueryable = await _authorRepository.GetQueryableAsync();
            authorQueryable = authorQueryable
                .WhereIf(!string.IsNullOrWhiteSpace(input.AuthorName), q => q.Name.Contains(input.AuthorName));

            var query = from book in queryable
                        join author in authorQueryable
                        on book.AuthorId equals author.Id
                        select new { book, author };

            var totalCount = await AsyncExecuter.CountAsync(query);
            query = query
                .OrderBy(NormalizeSorting(input.Sorting))
                .Skip(input.SkipCount)
                .Take(input.MaxResultCount);

            var queryResult = await AsyncExecuter.ToListAsync(query);

            var bookDtos = queryResult.Select(x =>
            {
                var bookDto = ObjectMapper.Map<Book, BookDto>(x.book);
                bookDto.AuthorName = x.author.Name;
                return bookDto;
            }).ToList();
            return new PagedResultDto<BookDto>(totalCount, bookDtos);
        }
        public async Task<ListResultDto<AuthorLookupDto>> GetAuthorLookupAsync()
        {
            var authors = await _authorRepository.GetListAsync();

            return new ListResultDto<AuthorLookupDto>(
                ObjectMapper.Map<List<Author>, List<AuthorLookupDto>>(authors)
            );
        }
        private static string NormalizeSorting(string sorting)
        {
            if (sorting.IsNullOrEmpty())
            {
                return $"book.{nameof(Book.Name)}";
            }

            if (sorting.Contains("authorName", StringComparison.OrdinalIgnoreCase))
            {
                return sorting.Replace(
                    "authorName",
                    "author.Name",
                    StringComparison.OrdinalIgnoreCase
                );
            }

            return $"book.{sorting}";
        }

        [Authorize(BookStorePermissions.Books.Import)]
        public async Task<FileContentResult> ImportTemplate()
        {
            ExcelPackage.LicenseContext = OfficeOpenXml.LicenseContext.Commercial;
            using (var excelPackage = new OfficeOpenXml.ExcelPackage())
            {
                var sh = excelPackage.Workbook.Worksheets.Add("书籍列表导入模板(列顺序不可调整)");
                int r = 1, c = 1;
                sh.Cells[r, c++].Value = "序号*";
                sh.Cells[r, c++].Value = "名称*";
                sh.Cells[r, c++].Value = "作者*";
                sh.Cells[r, c++].Value = "类型*";
                sh.Cells[r, c++].Value = "发布日期*";
                sh.Cells[r, c++].Value = "价格*";
                return new FileContentResult(excelPackage.GetAsByteArray(), System.Net.Mime.MediaTypeNames.Application.Octet)
                {
                    FileDownloadName = $"书籍列表导入模板.xlsx"
                };
            }
        }

        [Authorize(BookStorePermissions.Books.Import)]
        public async Task<int> Import([FromForm] IFormFile file)
        {
            var errMsg = new List<string>();
            List<Book> books = new List<Book>();
            var authorDics = (await GetAuthorLookupAsync()).Items.ToDictionary(k => k.Name, v => v.Id);
            var typeDics = GetAllType();
            ExcelPackage.LicenseContext = OfficeOpenXml.LicenseContext.Commercial;
            using (var excelPackage = new ExcelPackage(file.OpenReadStream()))
            {
                var sh = excelPackage.Workbook.Worksheets.FirstOrDefault();
                if (sh == null)
                    throw new BusinessException("无有效工作表！");

                int r = 1, c = 0;
                while (true)
                {
                    var book = new Book();
                    r++;
                    c = 0;

                    if (!int.TryParse(GetCellValue(sh, r, ++c), out var index))
                        break;

                    book.Name = GetCellValue(sh, r, ++c);
                    if (string.IsNullOrWhiteSpace(book.Name))
                    {
                        errMsg.Add($"第{r}行，列名：姓名 不能为空!");
                    }

                    var authorName = GetCellValue(sh, r, ++c);
                    if (string.IsNullOrWhiteSpace(authorName))
                    {
                        errMsg.Add($"第{r}行，列名：作者 不能为空!");
                    }
                    else if (!authorDics.TryGetValue(authorName, out var authorId))
                    {
                        errMsg.Add($"第{r}行，列名：作者 未知作者名称[{authorName}]!");
                    }
                    else 
                    {
                        book.AuthorId = authorId;
                    }
                        
                    var typeVal = GetCellValue(sh, r, ++c);
                    if (string.IsNullOrWhiteSpace(typeVal))
                    {
                        errMsg.Add($"第{r}行，列名：类型 不能为空!");
                    }
                    else if (!typeDics.TryGetValue(typeVal, out var type))
                    {
                        errMsg.Add($"第{r}行，列名：作者 未知类型名称[{typeVal}]!");
                    }
                    else 
                    {
                        book.Type = type;
                    }

                    var publishDate = GetCellValue(sh, r, ++c);
                    if (string.IsNullOrWhiteSpace(publishDate))
                    {
                        errMsg.Add($"第{r}行，列名：发布日期 不能为空!");
                    }
                    else if (!DateTime.TryParse(publishDate, out var pDate))
                    {
                        errMsg.Add($"第{r}行，列名：作者 未知时间格式[{publishDate}]!");
                    }
                    else if (pDate < new DateTime(1900, 1, 1) || pDate > DateTime.Now)
                    {
                        errMsg.Add($"第{r}行，列名：作者 时间范围[1900/1/1~至今]!");
                    }
                    else 
                    {
                        book.PublishDate = pDate;
                    }

                    var priceVal = GetCellValue(sh, r, ++c);
                    if (string.IsNullOrWhiteSpace(priceVal))
                    {
                        errMsg.Add($"第{r}行，列名：价格 不能为空!");
                    }
                    else if (!float.TryParse(priceVal, out var price))
                    {
                        errMsg.Add($"第{r}行，列名：价格 未知小数格式[{priceVal}]!");
                    }
                    else if (price < 0)
                    {
                        errMsg.Add($"第{r}行，列名：价格 必须大于0!");
                    }
                    else
                    {
                        book.Price = price;
                    }
                    books.Add(book);
                }
            }
            if (errMsg.Count != 0) 
            {
                throw new BusinessException($"导入书籍失败，\r\n" + string.Join("\r\n", errMsg));
            }
            await Repository.InsertManyAsync(books, true);
            return books.Count;
            Dictionary<string, BookType> GetAllType()
            {
                var result = new Dictionary<string, BookType>();
                var values = System.Enum.GetValues<BookType>();
                foreach (var value in values) 
                {
                    result[_localizer["Enum:BookType." + value.ToString()].Value] = value;
                }
                return result;
            }
            string GetCellValue(ExcelWorksheet sheet, int row, int column)
            {
                return sheet.Cells[row, column].Value?.ToString()?.Trim() ?? string.Empty;
            }
        }

        [Authorize(BookStorePermissions.Books.Export)]
        public async Task<FileContentResult> Export([FromQuery]GetBookListDto input)
        {
            input.SkipCount = 0;
            input.MaxResultCount = 1000;
            var exportDatas = await GetListAsync(input);
            ExcelPackage.LicenseContext = OfficeOpenXml.LicenseContext.Commercial;
            using (var excelPackage = new OfficeOpenXml.ExcelPackage())
            {
                var sh = excelPackage.Workbook.Worksheets.Add("书籍列表");
                int r = 1, c = 1;
                sh.Cells[r, c++].Value = "序号*";
                sh.Cells[r, c++].Value = "名称*";
                sh.Cells[r, c++].Value = "作者*";
                sh.Cells[r, c++].Value = "类型*";
                sh.Cells[r, c++].Value = "发布日期*";
                sh.Cells[r, c++].Value = "价格*";
                foreach (var item in exportDatas.Items)
                {
                    r++;
                    c = 1;
                    sh.Cells[r, c++].Value = (r - 1).ToString();
                    sh.Cells[r, c++].Value = item.Name;
                    sh.Cells[r, c++].Value = item.AuthorName;
                    sh.Cells[r, c++].Value = _localizer["Enum:BookType." + item.Type.ToString()].Value;
                    sh.Cells[r, c++].Value = item.PublishDate.ToString("yyyy/MM/dd");
                    sh.Cells[r, c++].Value = item.Price.ToString("f2");
                }
                return new FileContentResult(excelPackage.GetAsByteArray(), System.Net.Mime.MediaTypeNames.Application.Octet)
                {
                    FileDownloadName = $"书籍列表导入模板.xlsx"
                };
            }
        }
    }
}
